package com.finance.cooperate.dao.repository.primary;

import com.finance.cooperate.dao.entity.primary.App;
import com.finance.cooperate.dao.support.jpa.BaseRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @InterfaceName AppRepository
 * @Description app列表仓库
 * @Author shen
 * @Date 2023/8/6 16:23
 * @Modify ...
 */
@Repository
public interface AppRepository extends BaseRepository<App, Long> {


    // 最后一次
    @Query(value = "select MAX(a.id) as id, a.app_name, a.app_package_name, a.user_id, a.device_id, a.first_install_time, a.last_update_time, a.create_time, a.update_time, a.system_app from p_current_app a inner join (select user_id, device_id, max(create_time) max_create_time from p_current_app WHERE user_id = ?1 AND device_id = ?2 and create_time <= ?3 group by user_id, device_id) b on a.user_id=b.user_id and a.device_id = b.device_id and a.create_time = b.max_create_time WHERE a.user_id = ?1 and a.device_id = ?2 and a.create_time <= ?3 and a.system_app = 1 GROUP BY a.app_name, a.app_package_name, a.user_id, a.device_id, a.first_install_time, a.last_update_time, a.create_time, a.update_time, a.system_app", nativeQuery = true)
    List<App> getCurrentApp(String uid, String deviceId, Date createTime);


    // 去除厂家预安装
    @Query(value = "select MAX(a.id) as id, a.app_name, a.app_package_name, a.user_id, a.device_id, a.first_install_time, a.last_update_time, a.create_time, a.update_time, a.deleted, a.system_app from p_current_app a inner join (select user_id, device_id, max(create_time) max_create_time , MIN(first_install_time) min_first_install_time from p_current_app WHERE user_id = ?1 AND device_id = ?2 and create_time <= ?3 AND create_time > '2010-01-01 00:00:00' and system_app = 1 group by user_id, device_id) b on a.user_id=b.user_id and a.device_id = b.device_id and a.create_time = b.max_create_time WHERE a.user_id = ?1 and a.device_id = ?2 and a.create_time <= ?3 AND a.create_time > '2010-01-01 00:00:00' and a.system_app = 1 GROUP BY a.app_name, a.app_package_name, a.user_id, a.device_id, a.first_install_time, a.last_update_time, a.create_time, a.update_time, a.deleted, a.system_app,b.min_first_install_time HAVING LEFT(a.first_install_time, 13) != LEFT(b.min_first_install_time, 13)", nativeQuery = true)
    List<App> getAppDropPre(String uid, String deviceId, Date createTime);


}
